IF EXISTS (SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fnGetProviderNameParentMatch')
	BEGIN
		DROP  Function  fnGetProviderNameParentMatch
	END

GO

CREATE Function fnGetProviderNameParentMatch
(
	@providerNamePk int
)
returns uniqueidentifier
as
begin

	declare @name1Id nvarchar(300), @parentFk uniqueidentifier, @provPk int, @pos int, @nm nvarchar(300)
	declare @rankPk int, @fullName nvarchar(300), @canonical nvarchar(300), @rankSort int, @rnk nvarchar(100)
	declare @defNameId uniqueidentifier, @defPNId int, @provImportFk int
	declare @defGenNameId uniqueidentifier, @defGenPNId nvarchar(300), @highNameId uniqueidentifier, @genNameId nvarchar(300)
	
	--find parent based on rank and full name
	select @rankSort = RankSort, @rnk = RankName, @provImportFk = PNProviderImportFk,
		@rankPk = PNNameRankFk, @canonical = PNNameCanonical, @fullname = PNNameFull
	from tblProviderName
	inner join tblRank on RankPk = PNNameRankFk
	where PNPk = @providerNamePk
	
	select @defNameId = ProviderImportHigherNameId, 
		@defPNId = ProviderImportHigherPNId,
		@defGenNameId = ProviderImportGenusNameId, 
		@defGenPNId = ProviderImportGenusPNId
	from tblProviderImport
	where ProviderImportPk = @provImportFk
	
	set @highNameId = @defNameId
	set @genNameId = @defGenNameId
	
	if (@defPNId is not null)
	begin
		select @highNameId = isnull(PNNameFk, @highNameId) 
		from vwProviderName 
		where PNNameId = @defPNId and ProviderPk = @provPk		
	end
	
	if (@defGenPNId is not null)
	begin
		select @genNameId = isnull(PNNameFk, @genNameId) 
		from vwProviderName 
		where PNNameId = @defGenPNId and ProviderPk = @provPk
	end
	
		
	if (@rankSort <= 2000) --family or above, use higher name id
	begin
		if (@highNameId is not null)
		begin
			select @parentFk = @highNameId
		end
		else
		begin
			select @parentFk = NameGuid from tblName where NameFull = 'Plantae'
		end
	end
	else if (@rankSort < 3000) --infra family
	begin
		if (charindex(' subfam', @fullName) <> 0 or 
			charindex(' supertrib', @fullName) <> 0 or
			charindex(' trib', @fullName) <> 0 or
			charindex(' subtrib', @fullName) <> 0)
		begin
			set @pos = charindex(' ', @fullName)
			set @nm = substring(@fullName, 0, @pos)
			select top 1 @parentFk = NameGuid
			from tblName
			where NameCanonical = @nm and NameRankFk = 7
		end		
	end
	else if (@rankSort = 3000) --genus
	begin
		if (@genNameId is not null)
		begin
			select @parentFk = @genNameId
		end
		else
		begin
			select @parentFk = NameGuid from tblName where NameCanonical = 'Compositae'
		end 
	end
	else if (@rankSort < 4200) --infra generic
	begin
		if (charindex(' subgen', @fullName) <> 0 or 
			charindex(' sect', @fullName) <> 0 or
			charindex(' subsect', @fullName) <> 0 or
			charindex(' ser', @fullName) <> 0)
		begin
			set @pos = charindex(' ', @fullName)
			set @nm = substring(@fullName, 0, @pos)
			select top 1 @parentFk = NameGuid
			from tblName
			where NameCanonical = @nm and NameRankFk = 8
		end	
	end
	else if (@rankSort = 4200) --species
	begin
		set @pos = charindex(' ', @fullName)
		set @nm = substring(@fullName, 0, @pos)
		select top 1 @parentFk = NameGuid
		from tblName
		where NameCanonical = @nm and NameRankFk = 8		
	end
	else if (@rankSort = 4400) --subspecies
	begin
		set @pos = charindex(' subsp', @fullName)
		set @nm = substring(@fullName, 0, @pos)
		select top 1 @parentFk = NameGuid
		from tblName
		where NameFull = @nm and NameRankFk = 24		
		
		if (@parentfk is null)
		begin
			select top 1 @parentFk = NameGuid
			from tblName
			where NameFull like @nm + ' %' and NameRankFk = 24
		end
	end
	else if (@rankSort = 4600) --variety
	begin
		if (charindex(' subsp', @fullName) <> 0)
		begin		
			set @pos = charindex(' var', @fullName)
			set @nm = substring(@fullName, 0, @pos)
			select top 1 @parentFk = NameGuid
			from tblName
			where NameFull = @nm and NameRankFk = 35
			
			if (@parentfk is null)
			begin
				select top 1 @parentFk = NameGuid
				from tblName
				where NameFull like @nm + ' %' and NameRankFk = 35
			end
		end
		else
		begin
			set @pos = charindex(' var', @fullName)
			set @nm = substring(@fullName, 0, @pos)
			select top 1 @parentFk = NameGuid
			from tblName
			where NameFull = @nm and NameRankFk = 24		
			
			if (@parentfk is null)
			begin
				select top 1 @parentFk = NameGuid
				from tblName
				where NameFull like @nm + ' %' and NameRankFk = 24
			end
		end
	end
	
	return @parentFk
end
GO


GRANT EXEC ON fnGetProviderNameParentMatch TO PUBLIC

GO


